﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using DBUtil.Provider.SqlServer.MetaData;
using DBUtil.MetaData;
using System.Linq;
using DBUtil.Generators;
using System.Collections.Concurrent;
using System.Threading;
using System.Diagnostics;
using DBUtil.Expressions;
using DBUtil.Provider.SqlServer.Expressions;
using DotNetCommon;

namespace DBUtil.Provider.SqlServer
{
    /// <summary>
    /// Microsoft SQL Server操作对象
    /// </summary>
    public partial class SqlServerAccess : DBAccess
    {
        #region 创建IDbCommand
        /// <summary>
        /// 创建IDbCommand
        /// </summary>
        /// <returns></returns>
        protected override IDbCommand CreateCommand()
        {
            return new SqlCommand();
        }
        #endregion

        #region 创建IDataAdapter(根据命令对象)
        /// <summary>
        /// 创建IDataAdapter
        /// </summary>
        /// <param name="cmd">命令对象</param>
        /// <returns></returns>
        protected override IDataAdapter CreateAdapter(IDbCommand cmd)
        {
            return new SqlDataAdapter(cmd as SqlCommand);
        }
        #endregion

        #region 创建参数 CreatePara
        /// <summary>
        /// 创建参数
        /// </summary>
        /// <returns></returns>
        public override IDataParameter CreatePara()
        {
            return new SqlParameter();
        }
        #endregion

        #region SqlServer参数

        private string _dataBaseUserName = "";
        /// <summary>
        /// 操作当前数据库的用户名(区别于LoginUserName,就像dbo区别于sa一样)
        /// </summary>
        public override string UserName
        {
            get
            {
                if (string.IsNullOrWhiteSpace(_dataBaseUserName))
                {
                    lock (this)
                    {
                        if (string.IsNullOrWhiteSpace(_dataBaseUserName))
                        {
                            using (OpenLongConnectOnce())
                            {
                                _dataBaseUserName = SelectScalar<string>("select USER_NAME()");
                            };
                        }
                    }
                }
                return _dataBaseUserName;
            }
        }

        private string _loginUserName = "";
        /// <summary>
        /// 登录数据库使用的用户名
        /// </summary>
        public override string LoginUserName
        {
            get
            {
                if (string.IsNullOrWhiteSpace(_loginUserName))
                {
                    lock (this)
                    {
                        if (string.IsNullOrWhiteSpace(_loginUserName))
                        {
                            using (OpenLongConnectOnce())
                            {
                                _loginUserName = SelectScalar<string>("select SUSER_NAME()");
                            };
                        }
                    }
                }
                return _loginUserName;
            }
        }

        private string _dbname = string.Empty;
        /// <summary>
        /// 当前连接的数据库名称
        /// </summary>
        public override string DataBase
        {
            get
            {
                if (string.IsNullOrWhiteSpace(_dbname))
                {
                    lock (this)
                    {
                        if (string.IsNullOrWhiteSpace(_dbname))
                        {
                            using (OpenLongConnectOnce())
                            {
                                _dbname = SelectScalar<string>("select Db_Name()");
                            };
                        }
                    }
                }
                return _dbname;
            }
        }

        private string _dbversion = string.Empty;
        /// <summary>
        /// 数据库版本
        /// </summary>
        public override string DBVersion
        {
            get
            {
                if (string.IsNullOrWhiteSpace(_dbversion))
                {
                    lock (this)
                    {
                        if (string.IsNullOrWhiteSpace(_dbversion))
                        {
                            using (OpenLongConnectOnce())
                            {
                                _dbversion = SelectScalar<string>("select @@VERSION");
                            };
                        }
                    }
                }
                return _dbversion;
            }
        }

        private string _defaultSchem = string.Empty;
        /// <summary>
        /// 默认的架构/模式
        /// </summary>
        public override string DefaultSchema
        {
            get
            {
                if (string.IsNullOrWhiteSpace(_defaultSchem))
                {
                    lock (this)
                    {
                        if (string.IsNullOrWhiteSpace(_defaultSchem))
                        {
                            using (OpenLongConnectOnce())
                            {
                                _defaultSchem = SelectScalar<string>($"select default_schema_name from sys.database_principals where name ='{UserName}'");
                            };
                        }
                    }
                }
                return _defaultSchem;
            }
        }

        #endregion
        #region 初始化
        /// <summary>
        /// 初始化
        /// </summary>
        /// <param name="DBConn">连接字符串</param>
        /// <param name="Settings">使用的设置</param>
        public SqlServerAccess(string DBConn, DBSettings Settings)
        {
            this.DBConn = DBConn;
            this.Settings = Settings;
            this.Conn = new SqlConnection(DBConn);
            InitServerInfo();
        }

        private void InitServerInfo()
        {
            this.ParaPrefix = "@";
            this.DBType = "SQLSERVER";
        }
        #endregion

        #region GetSqlForPageSize
        /// <summary>获得分页的查询语句
        /// </summary>
        /// <param name="selectSql">查询sql如:select id,name from person where age>10</param>
        /// <param name="strOrder">排序字句如:order by id</param>
        /// <param name="PageSize">页面大小,如:10</param>
        /// <param name="PageIndex">页面索引从1开始,如:1</param>
        /// <returns>返回示例:select * from (select *,ROW_NUMBER() OVER(order by id) as RNO__ from (select id,name from person where age>10) as inner__ ) outer__ WHERE outer__.RNO__ BETWEEN (0*10+1) AND (1*10)</returns>
        public override string GetSqlForPageSize(string selectSql, string strOrder, int PageSize, int PageIndex)
        {
            string sql = "";
            if (this.IsSqlServerVersion2012Compatible())
            {
                //使用fetch分页
                sql = $"{selectSql} {strOrder} offset {(PageIndex - 1) * PageSize} rows fetch next {PageSize} rows only";
            }
            else
            {
                //使用ROW_NUMBER分页
                sql = $"select * from (select *,ROW_NUMBER() OVER({strOrder}) as RNO__ from ({selectSql}) as inner__ ) outer__ WHERE outer__.RNO__ BETWEEN ({PageIndex - 1}*{PageSize}+1) AND ({PageIndex}*{PageSize})";
            }
            return sql;
        }
        #endregion

        #region Judge系列: 判断表/视图/列/存储过程是否存在

        /// <summary>
        /// 判断存储过程是否存在
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <returns></returns>
        public override bool JudgeProcedureExist(string procName)
        {
            string sql = $"SELECT count(1) FROM sysobjects WHERE name='{procName}' and xtype='P'";
            int r = SelectScalar<int>(sql);
            return r > 0;
        }

        /// <summary>
        /// 判断触发器是否存在
        /// </summary>
        /// <param name="triggerName">触发器名称</param>
        /// <returns></returns>
        public override bool JudgeTriggerExist(string triggerName)
        {
            string sql = $"SELECT count(1) FROM sys.triggers WHERE name = N'{triggerName}'";
            int r = SelectScalar<int>(sql);
            return r > 0;
        }
        #endregion

        #region 基于数据库的Id和流水号生成器
        private static ConcurrentDictionary<string, bool> _initGenerators = new ConcurrentDictionary<string, bool>();
        private void EnsureInitGenerator()
        {
            if (!_initGenerators.TryGetValue(DBConn, out bool flag))
            {
                lock (typeof(DBAccess))
                {
                    if (!_initGenerators.TryGetValue(DBConn, out bool flag2))
                    {
                        var b = InitGenerator();
                        if (b)
                        {
                            _initGenerators.TryAdd(DBConn, true);
                        }
                    }
                }
            }
        }

        private bool InitGenerator()
        {
            if (!JudgeTableExist(Settings.DBCacheGeneratorLogTableName))
            {
                #region 新建表 Settings.DBCacheGeneratorLogTableName
                ExecuteSql($@"
create table {Settings.DBCacheGeneratorLogTableName}(
  id int identity(1,1),--错误序号
  err_proc  varchar(200),--出现错误的存储过程或 触发器的名称
  lock_str  varchar(200),--申请的锁资源锁
  err_num   int,--错误号
  err_severity  int,--严重性
  err_state int,--错误状态号  
  err_line  int,--导致错误的例程中的行号
  err_msg   varchar(200),--错误消息的完整文本
  err_time  datetime --错误发生时间
)");
                #endregion
            }
            if (!JudgeTableExist(Settings.DBCacheGeneratorIdTableName))
            {
                #region 新建表 Settings.DBCacheGeneratorIdTableName
                ExecuteSql($@"
create table {Settings.DBCacheGeneratorIdTableName}(
	 tablename varchar(50),
	 colname varchar(50),
	 currentid bigint,
	 primary key(tablename,colname)
)");
                #endregion
            }
            if (!JudgeProcedureExist(Settings.DBCacheGeneratorIdProcedureName))
            {
                var procName = Settings.DBCacheGeneratorIdProcedureName;
                var tableName = Settings.DBCacheGeneratorIdTableName;
                var errTableName = Settings.DBCacheGeneratorLogTableName;
                #region 新建存储过程 Settings.DBCacheGeneratorIdProcedureName
                ExecuteSql($@"
create proc {procName}
@tablename varchar(50),
@colname varchar(50),
@count int=1
as
begin tran
    declare @current bigint,@sql nvarchar(1000),@lockstr nvarchar(200), @result int;
    begin
	    -- 申请锁,1分钟
	    set @lockstr=@tablename+'_'+@colname;
	    EXEC @result =sp_getAppLock @resource=@lockstr,@lockMode='Exclusive',@lockOwner='Transaction',@lockTimeout='60000';
	    if(@result >= 0)
	    begin
		    begin try
			    --先从id表里查找
			    set @current = (select top 1 currentid from {tableName} where tablename = @tablename and colname = @colname)
			    if @current is null
			      --表里尚未缓存,从实际表里查找id列的最大值
			      begin
				    set @sql='select @current=max('+@colname+') from '+@tablename	
				    exec sp_executesql @sql,N'@current bigint output',@current output
				    if(@current is null)
					    --实际表里也没有值
					    begin
                            set @current=@count
						    insert into {tableName}(tablename, colname, currentid) values(@tablename, @colname, @current)
						    select @current
					    end
				    else
					    --实际表里有值
					    begin
						    set @current+=@count
						    insert into {tableName}(tablename, colname, currentid) values(@tablename, @colname, @current)
						    select @current
					    end    
			      end
			    else
				    --表里已经缓存
				    begin
					    set @current += @count
					    update {tableName} set currentid = @current where tablename = @tablename and colname = @colname
					    select @current
			       end
			    exec @result= sp_releaseapplock @resource=@lockstr,@lockOwner='Transaction';
                commit
                return
		    end try
		    begin catch
                rollback;
			    exec @result= sp_releaseapplock @resource=@lockstr,@lockOwner='Transaction';
			    INSERT INTO {errTableName}(err_proc,lock_str,err_num,err_severity,err_state,err_line,err_msg,err_time) 
				    values(ERROR_PROCEDURE(),@lockstr,ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(), ERROR_LINE() ,ERROR_MESSAGE(),GetDate());
                return;
		    end catch
	    end
	    else
	    begin
            rollback;
		    INSERT INTO {errTableName}(err_proc,lock_str,err_msg,err_time) 
				    values(ERROR_PROCEDURE(),@lockstr,'未获取到锁,申请锁返回:'+Convert(varchar,@result),GetDate());
            return;
	    end
    end");
                #endregion
            }
            if (!JudgeTableExist(Settings.DBCacheGeneratorSNOTableName))
            {
                #region 新建表(流水号生成) Settings.DBCacheGeneratorSNOTableName
                ExecuteSql($@"
create table {Settings.DBCacheGeneratorSNOTableName}(
	tablename varchar(50),
	colname varchar(50),
	nowstr datetime,
	currentno bigint,
	primary key(tablename,colname,nowstr)
)");
                #endregion
            }
            if (!JudgeProcedureExist(Settings.DBCacheGeneratorSNOProcedureName))
            {
                var procName = Settings.DBCacheGeneratorSNOProcedureName;
                var tableName = Settings.DBCacheGeneratorSNOTableName;
                var errTableName = Settings.DBCacheGeneratorLogTableName;
                #region 新建存储过程(流水号生成) Settings.DBCacheGeneratorSNOProcedureName
                ExecuteSql($@"
create proc {procName}
@tablename varchar(50),
@colname varchar(50),
@nowstr varchar(200),
@likestr varchar(200),
@startindex int, --注意:程序传入的startindex是以0起始,而数据库中是以1起始
@count int=1 --批量生成时的数量
as
declare @current bigint,@tempno nvarchar(200),@sql nvarchar(1000),@templen int,@lockstr nvarchar(200),@result int;

--清空超过30分钟的流水号生成控制
delete from {tableName} where tablename = @tablename and colname = @colname and nowstr<DATEADD(MINUTE,-30,convert(datetime,@nowstr, 20))
delete from {tableName} where currentno is null
begin tran
-- 申请锁,1分钟
set @lockstr=@tablename+'_'+@colname+'_'+@nowstr;
EXEC @result =sp_getAppLock @resource=@lockstr,@lockMode='Exclusive',@lockOwner='Transaction',@lockTimeout='60000';
if(@result >= 0)
begin
	begin try
		--先从流水号表里查找
		set @current = (select top 1 currentno from {tableName} where tablename = @tablename and colname = @colname and nowstr=@nowstr)
		if @current is null
		  --表里尚未缓存,从实际表里查找流水号列的最大值
		  begin
			set @sql='select top 1 @tempno='+@colname+' from '+@tablename +' where '+@colname+' like '''+@likestr+''' order by '+@colname +' desc'
			exec sp_executesql @sql,N'@tempno nvarchar(200) output',@tempno output
			if(@tempno is null)
				begin
					insert into {tableName}(tablename, colname,nowstr, currentno) values(@tablename, @colname,@nowstr, @count)
					select @count
				end
			else
				begin
					set @current=CONVERT(int,SUBSTRING(@tempno,@startindex+1,100))
					set @current+=@count
					insert into {tableName}(tablename, colname,nowstr, currentno) values(@tablename, @colname,@nowstr, @current)
					select @current
				end    
		  end
		else
			begin
				set @current += @count
				update {tableName} set currentno = @current where tablename = @tablename and colname = @colname and nowstr=@nowstr
				select @current
		   end
        exec @result= sp_releaseapplock @resource=@lockstr,@lockOwner='Transaction';
		commit;
		return;
	end try
	begin catch
		rollback;
		exec @result= sp_releaseapplock @resource=@lockstr,@lockOwner='Transaction';
		INSERT INTO {errTableName}(err_proc,lock_str,err_num,err_severity,err_state,err_line,err_msg,err_time) 
			values(ERROR_PROCEDURE(),@lockstr,ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(), ERROR_LINE() ,ERROR_MESSAGE(),GetDate());
        return;
	end catch
end
else
begin
    rollback;
	INSERT INTO {errTableName}(err_proc,lock_str,err_msg,err_time) 
			values(ERROR_PROCEDURE(),@lockstr,'未获取到锁,申请锁返回:'+Convert(varchar,@result),GetDate());
    return;
end
");
                #endregion
            }
            return true;
        }

        /// <summary>
        /// 根据指定的表名和列名生成Id
        /// </summary>
        /// <param name="tableName">指定的表名</param>
        /// <param name="colName">指定的列名</param>
        /// <returns></returns>
        public override long NewId(string tableName, string colName)
        {
            EnsureInitGenerator();
            var id = SelectScalar<long>($"exec {Settings.DBCacheGeneratorIdProcedureName} \"{tableName}\",\"{colName}\"");
            return id;
        }

        /// <summary>
        /// 根据指定的表名和列名批量生成Id
        /// </summary>
        /// <param name="tableName">指定的表名</param>
        /// <param name="colName">指定的列名</param>
        /// <param name="count">生成的Id的数量</param>
        /// <returns></returns>
        public override List<long> NewIds(string tableName, string colName, int count)
        {
            if (count < 1) throw new Exception("批量生成的数量最小为1!");
            EnsureInitGenerator();
            var id = SelectScalar<long>($"exec {Settings.DBCacheGeneratorIdProcedureName} \"{tableName}\",\"{colName}\",{count}");
            var res = new List<long>();
            for (long i = id - count + 1; i <= id; i++)
            {
                res.Add(i);
            }
            return res;
        }

        /// <summary>
        /// 重置一个表的Id生成控制
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <returns></returns>
        public override void ResetId(string tableName, string colName)
        {
            EnsureInitGenerator();
            Delete(Settings.DBCacheGeneratorIdTableName, $" and tablename={ParaPrefix}tablename and colname={ParaPrefix}colname", CreatePara("tablename", tableName), CreatePara("colname", colName));
        }

        /// <summary>
        /// 根据表名和列名生成流水号
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="format">流水号格式</param>
        /// <returns></returns>
        public override string NewSNO(string tableName, string colName, SerialFormat format)
        {
            var msg = SerialFormat.ValidFormat(format);
            if (!string.IsNullOrWhiteSpace(msg)) throw new Exception(msg);
            EnsureInitGenerator();
            var (likestr, snoNow, startindex) = SerialFormat.Parse(format, null, DotNetCommon.DistributeGenerator.MachineId);
            long no = SelectScalar<long>($"exec {Settings.DBCacheGeneratorSNOProcedureName} @tablename='{tableName}',@colname='{colName}',@nowstr='{snoNow.ToString("yyyy-MM-dd HH:mm:ss")}', @likestr='{likestr}',@startindex={startindex}");
            var chunk = format.Chunks.FirstOrDefault(i => i.Type == SerialFormatChunkType.SerialNo);
            var sno = likestr.Substring(0, startindex);
            var s = no.ToString();
            if (s.Length > chunk.Length)
            {
                sno += s;
            }
            else
            {
                sno += s.PadLeft(chunk.Length, '0');
            }
            return sno;
        }

        /// <summary>
        /// 根据表名和列名批量生成流水号
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="format">流水号格式</param>
        /// <param name="count">生成的流水号的数量</param>
        /// <returns></returns>
        public override List<string> NewSNOs(string tableName, string colName, SerialFormat format, int count)
        {
            if (count < 1) throw new Exception("批量生成的数量最小为1!");
            var msg = SerialFormat.ValidFormat(format);
            if (!string.IsNullOrWhiteSpace(msg)) throw new Exception(msg);
            EnsureInitGenerator();
            var (likestr, snoNow, startindex) = SerialFormat.Parse(format, null, DotNetCommon.DistributeGenerator.MachineId);
            long no = long.Parse(SelectScalar<string>($"exec {Settings.DBCacheGeneratorSNOProcedureName} @tablename='{tableName}',@colname='{colName}',@nowstr='{snoNow.ToString("yyyy-MM-dd HH:mm:ss")}', @likestr='{likestr}',@startindex={startindex},@count={count}"));
            var chunk = format.Chunks.FirstOrDefault(i => i.Type == SerialFormatChunkType.SerialNo);
            var res = new List<string>();
            for (long i = no - count + 1; i <= no; i++)
            {
                var sno = likestr.Substring(0, startindex);
                var s = i.ToString();
                if (s.Length > chunk.Length)
                {
                    sno += s;
                }
                else
                {
                    sno += s.PadLeft(chunk.Length, '0');
                }
                res.Add(sno);
            }
            return res;
        }

        /// <summary>
        /// 重置流水号
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        public override void ResetSNO(string tableName, string colName)
        {
            EnsureInitGenerator();
            Delete(Settings.DBCacheGeneratorSNOTableName, $" and tablename={ParaPrefix}tablename and colname={ParaPrefix}colname", CreatePara("tablename", tableName), CreatePara("colname", colName));
        }
        #endregion

        #region 数据库管理对象 Manage
        private DBManage _dbmanage = null;
        /// <summary>
        /// 数据库管理对象
        /// </summary>
        public override DBManage Manage => _dbmanage != null ? _dbmanage : _dbmanage = new SqlServerManage(this);
        #endregion

        #region 运算符
        /// <summary>
        /// 数据库日期时间运算符
        /// </summary>
        public override DateTimeExpression Expression_DateTime { get => SqlServerDateTimeExpression.Default; }
        #endregion

        #region 使用SqlBulkCopy批量插入数据
        /// <summary>
        /// 使用SqlBulkCopy批量插入数据
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="tableName">目的表名,如果为空,则去dt.TableName</param>
        public void BulkCopy(DataTable dt, string tableName = null)
        {
            if (string.IsNullOrWhiteSpace(tableName)) tableName = dt.TableName;
            if (string.IsNullOrWhiteSpace(tableName)) throw new Exception("必须指定要目的表名!");
            SqlBulkCopy sbc = null;
            if (IsTran)
            {
                sbc = new SqlBulkCopy((SqlConnection)Conn, SqlBulkCopyOptions.Default, (SqlTransaction)Tran);
            }
            else
            {
                sbc = new SqlBulkCopy((SqlConnection)Conn);
            }
            using (sbc)
            {
                using (OpenLongConnectOnce())
                {
                    sbc.BatchSize = dt.Rows.Count;
                    sbc.BulkCopyTimeout = 60 * 30;
                    sbc.DestinationTableName = dt.TableName;
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        sbc.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                    }
                    sbc.WriteToServer(dt);
                };
            }
        }

        #endregion

    }
}
